package com.dp.service.impl;

import java.io.Serializable;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityNotFoundException;
import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pub.SysProperty;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.BookConfig;
import com.dp.util.InterUtil;
import com.dp.vo.NewsExtendsChannel;
import com.dp.vo.NewsQuestion;
import com.dp.vo.NewsQuestionAnswer;
import com.dp.vo.NewsQuestionAnswerPK;
import com.dp.vo.TChannels;

@Service
@Transactional
public class NewsQuestionServiceImpl extends BaseImpl {

	/**
	 * 得到投票主题信息记录,分页
	 * 
	 * @return
	 */
	public List<NewsQuestion> findAllNewsQuestion(NewsQuestion newsQuestion,
			Integer firstrow, Integer maxrow) {
		List<NewsQuestion> queryList =new ArrayList<NewsQuestion>();
		Query query=null;
		try {
			StringBuffer sf = new StringBuffer(
				"SELECT n FROM NewsQuestion n WHERE 1=1 AND n.channleid is not null");
		if (newsQuestion != null) {

			if (newsQuestion.getChannleid() != null
					&& newsQuestion.getChannleid().getChannelId()
							.getChannelId().toString().trim().length() > 0) {
				sf.append(" AND n.channleid.channelId='"
						+ newsQuestion.getChannleid().getChannelId()
								.getChannelId() + "'");
			}
			if (newsQuestion.getQuestname() != null
					&& newsQuestion.getQuestname().toString().trim().length() > 0) {
				sf.append(" AND n.questname like '%"
						+ newsQuestion.getQuestname() + "%'");
			}
			if (newsQuestion.getQuestisok() != null
					&& newsQuestion.getQuestisok().toString().trim().length() > 0) {
				sf.append(" AND n.questisok='" + newsQuestion.getQuestisok()
						+ "'");
			}
			if (newsQuestion.getAnswermutiselect() != null
					&& newsQuestion.getAnswermutiselect().toString().trim()
							.length() > 0) {
				sf.append(" AND n.answermutiselect='"
						+ newsQuestion.getAnswermutiselect() + "'");
			}

		}
		sf.append(" order by questisok,questorder desc,questionid desc");
		query = em.createQuery(sf.toString());
		if (firstrow != null) {
			query.setFirstResult(firstrow);
		}
		if (maxrow != null) {
			query.setMaxResults(maxrow);
		}
			queryList = query.getResultList();
		}catch (EntityNotFoundException e) {
			logger.info("delete Invalid data:"+e.getMessage());
			if(deleteInvalidData()){
				queryList=findAllNewsQuestion(newsQuestion,firstrow,maxrow);
			}
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		return queryList;
	}

	public boolean deleteInvalidData(){
		Boolean resultb=true;
		try {
//			this.init();
			//删除无效投票选项
			this.sql="delete from news_question_answer where questionid in (select nq.questionid from news_question nq where nq.channleid not in (select channleid from news_extends_channel nec))";
			this.em.createNativeQuery(sql).executeUpdate();
			
			//删除无效投票
			this.sql="delete from news_question where channleid not in (select channleid from news_extends_channel nec)";
			this.em.createNativeQuery(sql).executeUpdate();
			
//			conn.commit(); 
		} catch (Exception e) {
			logger.info("deleteNewsGroupById fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
			
		}
		return resultb;
	}
	
	/**
	 * 增加投票主题
	 * 
	 * @param newsQuestion
	 * @param nqalist
	 *            选项列表
	 * @return
	 */
	public boolean addNewsQuestion(NewsQuestion newsQuestion,
			List<NewsQuestionAnswer> nqalist) {
		String jpql = "";
		Query query = null;
		jpql = "SELECT max(questorder) FROM NewsQuestion WHERE channleid.channelId='"
				+ newsQuestion.getChannleid().getChannelId().getChannelId()
				+ "'";
		query = em.createQuery(jpql);
		Integer questorder = 0;
		if (query.getSingleResult() != null) {
			questorder = Integer.parseInt(query.getSingleResult()
					.toString());
		}
		newsQuestion.setQuestorder(questorder + 1);

		NewsQuestion newsQuestion2 = em.merge(newsQuestion);
		// 插入投票选项
		for (NewsQuestionAnswer questionAnswer : nqalist) {
			jpql = "SELECT max(newsQuestionAnswerPK.answerid) FROM NewsQuestionAnswer";
			query = em.createQuery(jpql);
			Integer answerid = 0;
			if (query.getSingleResult() != null) {
				answerid = Integer.parseInt(query.getSingleResult()
						.toString());
			}
			questionAnswer
					.setNewsQuestionAnswerPK(new NewsQuestionAnswerPK(
							newsQuestion2.getQuestionid(), answerid + 1));
			em.merge(questionAnswer);
		}
		return true;
	}

	
	/**
	 * 修改投票
	 * 
	 * @param newsQuestion
	 * @param nqalist
	 * @return
	 */
	public boolean updateNewsQuestion(NewsQuestion newsQuestion,
			List<NewsQuestionAnswer> nqalist) {
		String jpql = "";
		Query query = null;
		NewsQuestion newsQuestion2 = em.merge(newsQuestion);

		// 删除原有选项
		jpql = "DELETE FROM NewsQuestionAnswer WHERE newsQuestion.questionid='"
				+ newsQuestion2.getQuestionid() + "'";
		query = em.createQuery(jpql);
		Object obj = query.executeUpdate();
		em.flush();
		em.clear();

		// 插入投票选项
		for (NewsQuestionAnswer questionAnswer : nqalist) {
			jpql = "SELECT max(newsQuestionAnswerPK.answerid) FROM NewsQuestionAnswer";
			query = em.createQuery(jpql);
			Integer answerid = 0;
			if (query.getSingleResult() != null) {
				answerid = Integer.parseInt(query.getSingleResult()
						.toString());
			}
			questionAnswer
					.setNewsQuestionAnswerPK(new NewsQuestionAnswerPK(
							newsQuestion2.getQuestionid(), answerid + 1));
			//System.out.println("questionAnswer:" + questionAnswer);
			em.merge(questionAnswer);
		}
		
		return true;
	}
	
	
	/**
	 * 得到投票主题相关栏目
	 * @return
	 */
	public List<TChannels> getChannelList() {
		List<TChannels> queryList=new ArrayList<TChannels>();
		try {
		String jpql = "SELECT ch FROM TChannels ch WHERE ch.status='"
				+ BookConfig.channel_status_enable + "' AND ch.channelType='"
				+ BookConfig.channel_type_question + "'  AND (functionName='"
				+ BookConfig.channel_song + "' OR functionName='"
				+ BookConfig.channel_last + "')";
		Query query = em.createQuery(jpql);
			queryList = query.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return queryList;
	}

	/**
	 * 根据TChannel id得到 NewsExtendsChannel
	 * 
	 * @param channleId
	 * @return
	 */
	public NewsExtendsChannel getNewsExtendsChannelByChannelId(String channleId) {
		Object obj=null;
		try {
		String jpql = "SELECT n FROM NewsExtendsChannel n WHERE 1=1 and n.channelId='"
				+ channleId + "'";
		Query query = em.createQuery(jpql);
		 	obj = query.getSingleResult();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		if (obj == null) {
			return null;
		} else {
			return (NewsExtendsChannel) obj;
		}
	}

	/**
	 * 排序
	 * 
	 * @param gvalue
	 * @return
	 */
	public boolean sortNewsQuestionItems(NewsQuestion newsQuestion2,
			String gvalue) {
		String jpql = "";
		Query query = null;
		String[] gArys = gvalue.split("\\|");

		// 所有排序号清空
		jpql = "UPDATE NewsQuestion n SET n.questorder=null WHERE n.channleid.channleid='"
				+ newsQuestion2.getChannleid().getChannleid() + "'";
		query = em.createQuery(jpql);
		query.executeUpdate();
		int questorder = 1;
		for (int i = gArys.length - 1; i >= 0; i--) {
			NewsQuestion newsQuestion = em.find(NewsQuestion.class, Integer
					.parseInt(gArys[i]));
			newsQuestion.setQuestorder(questorder);
			em.merge(newsQuestion);
			em.flush();
			em.clear();
			questorder++;
		}

		// 排序过程中，防止有人操作投票主题
		jpql = "SELECT n FROM NewsQuestion n WHERE  n.questorder is null AND n.channleid.channelId='"
				+ newsQuestion2.getChannleid().getChannelId()
						.getChannelId() + "'";
		query = em.createQuery(jpql);
		List<NewsQuestion> nqlist = query.getResultList();
		if (nqlist.size() > 0) {
			for (NewsQuestion newsQuestion : nqlist) {
				newsQuestion.setQuestorder(questorder);
				questorder++;
			}
		}

		return true;
	}
	
	/**
	 * 详情 专用
	 * @param newsQuestion
	 * @return
	 */
	@Transactional(readOnly = true)
	public List<NewsQuestionAnswer> getNewsQuestionAnswerListByNqid2(
			NewsQuestion newsQuestion) {
		List<NewsQuestionAnswer> nqalist=new ArrayList<NewsQuestionAnswer>();
		try {
			String jpql = "SELECT n FROM NewsQuestionAnswer n WHERE newsQuestion.questionid=:questionid";
			Query query = em.createQuery(jpql);
			query.setParameter("questionid", newsQuestion.getQuestionid());
			List<NewsQuestionAnswer> queryList = query.getResultList();
			int Totalanswerpoint=0;
			for(NewsQuestionAnswer questionAnswer:queryList){
				Totalanswerpoint+=questionAnswer.getAnswerpoint();
			}
			
			for(NewsQuestionAnswer questionAnswer:queryList){
				questionAnswer.setAnswernum(questionAnswer.getAnswerpoint()+"("+percent(questionAnswer.getAnswerpoint(),Totalanswerpoint)+")");
				double d=(questionAnswer.getAnswerpoint()/(double)Totalanswerpoint)*200;
				questionAnswer.setAnswerpoint((int)d);
				//System.out.println("百分比:"+percent(questionAnswer.getAnswerpoint(),Totalanswerpoint));
				//System.out.println("宽度："+questionAnswer.getAnswerpoint());
				nqalist.add(questionAnswer);
			}
		
		} catch (Exception e) {
			e.printStackTrace();
		}
		return nqalist;
	}
	
	public List<NewsQuestionAnswer> getNewsQuestionAnswerListByNqid(
			NewsQuestion newsQuestion) {
		List<NewsQuestionAnswer> queryList=new ArrayList<NewsQuestionAnswer>();
		try {
		String jpql = "SELECT n FROM NewsQuestionAnswer n WHERE newsQuestion.questionid=:questionid";
		Query query = em.createQuery(jpql);
		query.setParameter("questionid", newsQuestion.getQuestionid());
		 queryList = query.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return queryList;
	}

	
	/**
	 * 获得投票主题栏目列表
	 * @param hashMap
	 * @return  ArrayList
	 */
	/*public ArrayList getNewsQuestionChannelList(DataMap hashMap) {
        String sql = "select ch.* from t_channels ch WHERE 1=1 " +
        		"and channel_type='"+BookConfig.channel_type_question+"' " +
        		"and status='"+BookConfig.channel_status_enable+"' " +
        		"and (function_name='"+BookConfig.channel_song+"' OR function_name='"+BookConfig.channel_last+"')";
        Object employee_id = hashMap.get("employee_id");
        Object role_id = hashMap.get("role_id");
        if(role_id != null)
        {
	        if (!role_id.equals("") && StringIsNotNull(employee_id)) {
	            sql = "select ch.* from t_channels ch,t_user_channels uc WHERE 1=1 " +
	            "and channel_type='"+BookConfig.channel_type_question+"' " +
	            "and ch.channel_id=uc.channel_id " +
	            "and status='"+BookConfig.channel_status_enable+"' " +
	            "and (function_name='"+BookConfig.channel_last+"' OR function_name='"+BookConfig.channel_song+"') " +
	            "and uc.employee_id='" + employee_id + "'";
	        }
        }
        try {
            return new DBOperate().queryALLResultSet(sql);
        } catch (Exception e) {
        	logger.info(" getChannelList fail !!!" +e.getMessage());
            return null;
        }
        
    }*/
	
	/**
	 * 计算百分比
	 * @param p1
	 * @param p2
	 * @return
	 */
	private  String percent(double p1,double p2){
		  String str;
		  double p3=0;
		  if(p2!=0){
			  p3=p1/p2;
		  }
		  NumberFormat nf=NumberFormat.getPercentInstance();
		  if(p1==0){
			  nf.setMinimumFractionDigits(0);
		  }else{
			  nf.setMinimumFractionDigits(2);
		  }
		  str=nf.format(p3);
		  return str;
	}
	
	/**
	 * 得到所有问卷/投票类型channelid拼接字符串
	 * @return
	 */
	public String getNewsExtendsChannelidStr() {
		String channelidStr="";
		List<NewsExtendsChannel> queryList=new ArrayList<NewsExtendsChannel>();
		try {
			String jpql = "SELECT nc FROM NewsExtendsChannel nc,TChannels ch WHERE nc.channelId.channelId=ch.channelId AND ch.status='"
					+ BookConfig.channel_status_enable + "' AND ch.channelType='"
					+ BookConfig.channel_type_question + "'  AND (ch.functionName='"
					+ BookConfig.channel_song + "' OR ch.functionName='"
					+ BookConfig.channel_last + "')";
			Query query = em.createQuery(jpql);
			queryList = query.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(queryList.size()>0){
			for(NewsExtendsChannel t:queryList){
				if("".equals(channelidStr)){
					channelidStr=""+t.getChannleid();
 	    	   }else{
 	    		  channelidStr+=","+t.getChannleid();
 	    	   }
			}
		}
		return channelidStr;
	}

	@SuppressWarnings("unchecked")
	public Object get(Class clazz, Serializable id) {
		Object o=null;
		try {
			o = this.em.find(clazz, id);
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
		if (o == null) {
			String msg = "Uh oh, '" + clazz + "' object with id '" + id
					+ "' not found...";
			logger.warn(msg);
			throw new EntityNotFoundException(msg);
		}
		return o;
	}


	/**
	 * 刷新缓存publishtitle时
	 * 5分钟立即发布频道定时器和3点，17点更新频道缓存定时器调用
	 */
	public void rePublishtitleURL(String channelidlist){
	    String url="";
	    for(int i=1;i<1000;i++)
        {
            url=SysProperty.getPrep("sysserverurl."+i);
            if(url==null||"".equals(url)){
            	break;  
            }     
            url+="action/server!publishtitle.do?channelidlist="+channelidlist;
            	 try{
                     URL testUrl = new URL(url);
                     HttpURLConnection conn = (HttpURLConnection) testUrl.openConnection();
   			      conn.setRequestMethod("POST");
   				  int ret = conn.getResponseCode();
   				  conn.disconnect();
   				logger.info(ret != 200?InterUtil.rb.getString("src.refPorErr")+"："+url:InterUtil.rb.getString("src.refPorSuc")+"："+url);
   		         } catch (Exception e) {	logger.error(InterUtil.rb.getString("src.refPorErr")+"："+url+",Connection refused: connect");}	    		
           
        }
      
	 }
}
